/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package models.DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import models.DBHandler;
import models.beans.Request;

/**
 *
 * @author Hoang Dong Tien
 */
public class RequestDAO {

    public static List<Request> getAllRequestsByCondition(String whereClause) throws ClassNotFoundException, SQLException {
        List<Request> requests = new ArrayList<Request>();
        ResultSet rs = null;
        Connection oconn = DBHandler.getConnection();
        StringBuilder sb = new StringBuilder("SELECT * FROM [Requests] ");
        if(whereClause != null){
            sb.append(whereClause);
        }
        PreparedStatement pstmt = oconn.prepareStatement(sb.toString());
        rs = pstmt.executeQuery();

        Request requestTmp = null;
        while(rs.next()){
            requestTmp = new Request();
            requestTmp.setRequestUserId(rs.getInt("RequestUserId"));
            requestTmp.setEventId(rs.getInt("EventId"));
            requestTmp.setStatus(rs.getNString("Status"));

            requests.add(requestTmp);
        }

        DBHandler.closeDBObj(rs);
        DBHandler.closeDBObj(pstmt);
        DBHandler.closeDBObj(oconn);

        return requests;
    }

    public static int addNewRequest(Request newrequest) throws ClassNotFoundException, SQLException {
        int result = 0;

        Connection oconn = DBHandler.getConnection();
        PreparedStatement pstmt = oconn.prepareStatement("INSERT INTO [BluePumpkin].[dbo].[Requests]([RequesterUserId],[EventId],[Status]) VALUES(?,?,?)");
        pstmt.setInt(1, newrequest.getRequestUserId());
        pstmt.setInt(2, newrequest.getEventId());
        pstmt.setNString(2, newrequest.getStatus());
        result = pstmt.executeUpdate();

       
        DBHandler.closeDBObj(pstmt);
        DBHandler.closeDBObj(oconn);

        return result;
    }

    public static int updateRequest(Request newrequest) throws ClassNotFoundException, SQLException {
        int result = 0;

        Connection oconn = DBHandler.getConnection();
        PreparedStatement pstmt = oconn.prepareStatement("UPDATE [Requests] SET ([Status]=?) WHERE [RequesterUserId]=? AND [EventId] = ");
        pstmt.setNString(1, newrequest.getStatus());
        pstmt.setInt(2, newrequest.getRequestUserId());
        pstmt.setInt(3, newrequest.getEventId());

        result = pstmt.executeUpdate();
        DBHandler.closeDBObj(pstmt);
        DBHandler.closeDBObj(oconn);
        return result;
    }
    
    public static int deleteRequest(int requestUserId, int eventId) throws ClassNotFoundException, SQLException {
        int result = 0;

        Connection oconn = DBHandler.getConnection();
        PreparedStatement pstmt = oconn.prepareStatement("DELETE FROM [Requests] WHERE RequestUserId=?  AND EventId=? ");
        pstmt.setInt(1, requestUserId);
         pstmt.setInt(1, eventId);
        result = pstmt.executeUpdate();
        DBHandler.closeDBObj(pstmt);
        DBHandler.closeDBObj(oconn);
        return result;
    }
    
    public static int deleteRequest(Request request) throws ClassNotFoundException, SQLException {
        return deleteRequest(request.getRequestUserId(), request.getEventId());
    }

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        List<Request> requests = getAllRequestsByCondition(null);
        for (Request request : requests){
//            System.out.println("Request Name : " + request.setStatus(null) + "\n");
        }
    }
}
